package prg421.lectures.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcInsertExample
{
	public static void main(String[] args)
	{
		try
		{
			// Load Derby Driver
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

			// Create Connection
			Connection conn = DriverManager
					.getConnection("jdbc:derby:data/Derby;create=true");

			try
			{

				// Create table and insert data
				sql(conn,
						"CREATE TABLE Sample (ID INTEGER PRIMARY KEY, Name VARCHAR(256))");

				sql(conn, "INSERT INTO Sample VALUES(1, 'George Washington')");
				sql(conn, "INSERT INTO Sample VALUES(2, 'Thomas Jefferson')");
				sql(conn, "INSERT INTO Sample VALUES(3, 'John Adams')");

				// Select from the row with ID = 2
				PreparedStatement stmt = conn
						.prepareStatement("SELECT ID,NAME FROM sample WHERE ID=?");
				stmt.setString(1, "2");

				// Execute the query
				ResultSet results = stmt.executeQuery();

				// Read and Display Data
				while (results.next())
				{
					int id = results.getInt("ID");
					String name = results.getString("NAME");
					System.out.println(id + ": " + name);
				}

				results.close();
				stmt.close();

			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				// Drop the table
				sql(conn, "DROP TABLE Sample");

				// Close Connection
				conn.close();
			}
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
	}

	private static void sql(Connection conn, String sql) throws SQLException
	{
		PreparedStatement createStmt = conn.prepareStatement(sql);
		createStmt.execute();
		createStmt.close();
	}
}
